package ktwl.code.utils;

import com.alibaba.fastjson.JSONObject;

import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

/**
 * 数据处理
 *
 * @author Name on 2019/8/23 11:24
 * @version 1.0
 */
public class DbHandle {

    private static JSONObject object = null;

    /**
      * 处理编辑sql on 2019/8/26 17:37
      *
      * @param sql sql
      * @return JSONObject JSONObject
      * @throws Exception Exception
      */
    public static JSONObject handleUpdate(String sql) throws Exception{
        if(!(sql.contains("update") || sql.contains("UPDATE"))){
            throw new Exception("不是update的sql");
        }
        JSONObject object = new JSONObject();
        if(!(sql.contains("set") || sql.contains("SET"))){
            object.put("sql",sql);
            return object;
        }
        if(sql.contains("left join") || sql.contains("LEFT JOIN")){
            object.put("sql",sql);
            return object;
        }
        if(sql.contains("select") || sql.contains("SELECT")){
            object.put("sql",sql);
            return object;
        }
        String value = "";
        if(sql.contains("set")){
            value = "set";
        }
        if(sql.contains("SET")){
            value = "SET";
        }
        String s = sql.substring(sql.lastIndexOf(value) + 3);
        String s1 = sql;
        s1 = s1.replace("\"","");
        s1 = s1.replace("\'","");
        if(s.contains("where")){
            s = s.substring(0,s.lastIndexOf("where"));
        }
        if(s.contains("WHERE")) {
            s = s.substring(0,s.lastIndexOf("WHERE"));
        }
        String[] strings = s.split(",");
        JSONObject object1 = new JSONObject();
        for(int i = 0;i <strings.length;i++){
            strings[i] = strings[i].replace("\"","");
            strings[i] = strings[i].replace("\'","");
            strings[i] = strings[i].substring(strings[i].lastIndexOf("=") + 1);
            object1.put(String.valueOf(i),strings[i].trim());
            s1 = s1.replace(strings[i].trim(),"?");
        }
        object.put("value",object1);
        object.put("sql",s1);
        return object;
    }

    /**
      * 处理insertSql on 2019/8/26 10:40
      *
      * @param sql sql
      * @return JSONObject JSONObject
      * @throws Exception Exception
      */
    public static JSONObject handleInsert(String sql)throws Exception{
        if(!(sql.contains("insert") || sql.contains("INSERT"))){
            throw new Exception("不是insert的sql");
        }
        JSONObject object = new JSONObject();
        if(!(sql.contains("values") || sql.contains("VALUES"))){
            object.put("sql",sql);
            return object;
        }
        if(sql.contains("select") || sql.contains("SELECT")){
            object.put("sql",sql);
            return object;
        }
        String value = "";
        if(sql.contains("values")){
            value = "values";
        }
        if(sql.contains("VALUES")){
            value = "VALUES";
        }
        String s = sql;
        String s1 = s;
        s = s.substring(s.lastIndexOf(value));
        s = s.substring(s.lastIndexOf("(") + 1,s.lastIndexOf(")"));
        s = s.replace("\'","");
        s = s.replace("\"","");
        s1 = s1.replace("\'","");
        s1 = s1.replace("\"","");
        String[] strings = s.split(",");
        JSONObject jsonValue = new JSONObject();
        for(int i = 0;i < strings.length;i++){
            jsonValue.put(String.valueOf(i),strings[i]);
            s1 = s1.replace(strings[i],"?");
        }
        object.put("sql",s1);
        object.put("value",jsonValue);
        return object;
    }

    /**
      * 带参查询处理 on 2019/8/23 11:26
      *
      * @param rs ResultSet
      * @param metaData ResultSetMetaData
      * @param strings 进行分级的参数
      * @return List<JSONObject>
      * @throws SQLException SQLException
      */
    public static List<JSONObject> handleParameterQuery(ResultSet rs, ResultSetMetaData metaData, List<String> strings) throws SQLException {
        List<JSONObject> objects = new ArrayList<>();
        while(rs.next()){
            JSONObject subObject = new JSONObject();
            boolean b = false;
            String isSublevel = "";
            StringBuilder sign = new StringBuilder("");
            for(int i = 1;i <= metaData.getColumnCount();i++){
                String name = metaData.getColumnLabel(i);
                String value = rs.getString(name);
                //与之前的数据是否一致
                if(i == 1){
                    object = noeEqual(objects,value,name);
                    if(object == null){
                        object = new JSONObject();
                    }else{
                        b = true;
                    }
                }
                //是否到达下级
                String s = isSublevel(strings,name);
                if(!"".equals(s)){
                    if(!s.equals(isSublevel)) {
                        isSublevel = s;
                    }
                }
                if("".equals(isSublevel)){
                    if(!b){
                        object.put(name, value == null || "".equals(value)?"":value);
                    }
                }else{
                    List<JSONObject> list = null;
                    if(!isSublevel.equals(sign.toString())){
                        if("".equals(sign.toString())) {
                            if (object.get(isSublevel) == null || "".equals(object.get(isSublevel))) {
                                list = new ArrayList<>();
                                sublevel(subObject,list,isSublevel,sign,value,name);
                                object.put(isSublevel,list);
                            }else{
                                list = JSONObject.parseObject(object.getString(isSublevel),List.class);
                                sublevel(subObject,list, isSublevel, sign, value, name);
                                object.put(isSublevel,list);
                            }
                        }else {
                            if (subObject.get(isSublevel) == null || "".equals(subObject.get(isSublevel))) {
                                list = new ArrayList<>();
                                subObject.put(isSublevel,list);
                                subObject = new JSONObject();
                                sublevel(subObject,list,isSublevel,sign,value,name);
                            }else{
                                list = JSONObject.parseObject(subObject.getString(isSublevel),List.class);
                                JSONObject newjson = new JSONObject();
                                sublevel(newjson,list,isSublevel,sign,value,name);
                                subObject.put(isSublevel,list);
                                subObject = newjson;
                            }
                        }
                    }else {
                        sublevel(subObject, list, isSublevel, sign, value, name);
                    }
                }
            }
            if(!b) {
                objects.add(object);
            }
        }
        return objects;
    }

    /**
     * 子级 on 2019/8/22 17:56
     *
     * @param subObject 子级json对象
     * @param subList 子级集合
     * @param isSublevel 当前子级
     * @param sign 前一个子级
     * @param value 当前值
     * @param name 当前名称
     * @throws SQLException SQLException
     */
    private static void sublevel(JSONObject subObject,List<JSONObject> subList,String isSublevel,StringBuilder sign,String value,String name) throws SQLException{
        if(!isSublevel.equals(sign.toString())){
            subObject = getSubLevel(subObject,subList,value,name);
            sign.delete(0,sign.length());
            sign.append(isSublevel);
        }
        if(subObject.get(name) == null || "".equals(subObject.get(name))){
            subObject.put(name,value == null || "".equals(value)?"":value);
        }
    }

    /**
     * 查找下级 on 2019/8/23 10:57
     *
     * @param subObject 子级json对象
     * @param subList 子级集合
     * @param value 当前值
     * @param name 当前名称
     * @return JSONObject
     */
    private static JSONObject getSubLevel(JSONObject subObject,List<JSONObject> subList,String value,String name){
        if(subList == null){
            return subObject;
        }
        if(subList.size() <= 0){
            subList.add(subObject);
            return subObject;
        }
        for(JSONObject jsonObject:subList){
            if(jsonObject.get(name).equals(value)){
                removeJson(subObject);
                subObject.putAll(jsonObject);
                subList.remove(jsonObject);
                subList.add(subObject);
                return subObject;
            }
        }
        removeJson(subObject);
        subList.add(subObject);
        return subObject;
    }

    /**
      * 清除json数据 on 2019/8/26 9:58
      *
      * @param jsonObject jsonObject
      */
    private static void removeJson(JSONObject jsonObject){
        Iterator<String> keys = jsonObject.keySet().iterator();
        String[] strings = new String[jsonObject.size()];
        for(int i = 0;keys.hasNext();i++){
            strings[i] = keys.next();
        }
        for(String s:strings){
            jsonObject.remove(s);
        }
    }

    /**
     * 是否到达下级 on 2019/8/22 17:06
     *
     * @param strings 下级集合
     * @param name 下级名称
     * @return String
     */
    private static String isSublevel(List<String> strings,String name){
        for(String s:strings){
            if(s.equals(name)){
                return name;
            }
        }
        return "";
    }



    /**
     * 查询相等数据 on 2019/8/22 12:18
     *
     * @param objects objects
     * @param rs rs
     * @param metaData metaData
     * @return JSONObject
     * @throws SQLException SQLException
     */
    private static JSONObject noeEqual(List<JSONObject> objects,String rs,String metaData) throws SQLException{
        for(JSONObject jsonObject:objects){
            if(jsonObject.get(metaData).equals(rs)){
                return jsonObject;
            }
        }
        return null;
    }

    /**
     * 无下级查询处理 on 2019/8/22 12:17
     *
     * @param rs rs
     * @param metaData metaData
     * @return List<JSONObject>
     * @throws SQLException SQLException
     */
    public static List<JSONObject> handleNoGinsengQuery(ResultSet rs,ResultSetMetaData metaData) throws SQLException{
        List<JSONObject> objects = new ArrayList<>();
        while(rs.next()){
            JSONObject object = new JSONObject();
            for(int i = 1;i <= metaData.getColumnCount();i++){
                Object o = rs.getObject(metaData.getColumnLabel(i));
                object.put(metaData.getColumnLabel(i),o == null?"":o);
            }
            objects.add(object);
        }
        return objects;
    }
}